"""project_person_relation"""
from datetime import datetime
from flask import g
from psycopg2 import extras
import pytz



def info(verify_code=None, id_card=None, status=None):
    """用户信息"""
    with g.db_conn.cursor(cursor_factory=extras.RealDictCursor) as cursor:
        base_query = """
            SELECT 
                ppr.id, ppr.status, ppr.verify_code, ppr.signin_at, ppr.signout_at, ppr.role_id,
                p.name, p.id_card, p.face_pic, p.phone, p.work_org, p.hk_visitor_order_id,
                pr.project_code, pr.room_id, pr.project_name, ppr.project_id,
                SUBSTRING(qrcode_base64 FROM '^(data:[^;]+;base64,)') qrcode_base64_type,
                SUBSTRING(qrcode_base64 FROM 'base64,(.*)$') qrcode_base64_content,
                CASE s.inspect_room_id WHEN 1 THEN '监督一室' WHEN 2 THEN '监督二室' WHEN 3 THEN '监督三室' ELSE '' END room_name
            FROM 
                project_person_relation ppr
            JOIN 
                persons p 
                ON p.id = ppr.person_id
            JOIN 
                projects pr 
                ON pr.id = ppr.project_id
            JOIN 
                seat s
                ON split_part(pr.room_id, '-', 1) = s.code
            WHERE ppr.deleted_at IS NULL AND p.deleted_at IS NULL AND pr.deleted_at IS NULL AND s.deleted_at IS NULL
        """
        verify_code_query = f" AND ppr.verify_code = '{verify_code}'" if verify_code is not None else ""
        id_card_query = f" AND p.id_card = '{id_card}'" if id_card is not None else ""
        status_query = f" AND ppr.status = {status}" if status is not None else ""

        query = f"{base_query}{verify_code_query}{id_card_query}{status_query} ORDER BY id DESC"
        cursor.execute(query)
        return cursor.fetchall()


def exists(project_id, person_id):
    """检查人员是否存在"""
    with g.db_conn.cursor() as cursor:
        query = "SELECT EXISTS (SELECT 1 FROM project_person_relation WHERE project_id = %s AND person_id = %s AND deleted_at IS NULL)"
        cursor.execute(query, (project_id, person_id))
        return cursor.fetchone()[0]


def update(data, project_id, person_id):
    """更新人员"""
    current_timestamp = int(datetime.now(pytz.timezone('Asia/Shanghai')).timestamp())
    with g.db_conn.cursor() as cursor:
        set_data = []
        params = []
        if 'person_id' in data:
            set_data.append("person_id = %s")
            params.append(data['person_id'])

        if 'role_id' in data:
            set_data.append("role_id = %s")
            params.append(data['role_id'])

        if 'verify_code' in data:
            set_data.append("verify_code = %s")
            params.append(data['verify_code'])

        set_data.append("updated_at = %s")
        params.append(current_timestamp)

        set_clause = ", ".join(set_data)
        update_query = f"UPDATE project_person_relation SET {set_clause} WHERE project_id = %s AND person_id = %s RETURNING id"
        params.append(project_id)
        params.append(person_id)
        cursor.execute(update_query, params)
        g.db_conn.commit()
        return cursor.fetchone()[0]


def create(data, project_id, person_id):
    """创建关联"""
    with g.db_conn.cursor() as cursor:
        current_timestamp = int(datetime.now(pytz.timezone('Asia/Shanghai')).timestamp())
        data['verify_code'] = None if 'verify_code' not in data else data['verify_code']
        data['role_id'] = None if 'role_id' not in data else data['role_id']
        insert_query = """
            INSERT INTO 
                project_person_relation(project_id, person_id, created_at, verify_code, role_id) 
            VALUES (%s, %s, %s, %s, %s) 
            RETURNING id
        """
        cursor.execute(insert_query, (project_id, person_id, current_timestamp, data['verify_code'], data['role_id']))
        g.db_conn.commit()
        return cursor.fetchone()[0]


def update_status(cursor, project_id):
    """更新状态"""
    cursor.execute("UPDATE project_person_relation SET status = 1 WHERE project_id = %s AND status = 0 RETURNING id", (project_id,))


def destroy(cursor, project_id, person_id):
    """删除关联"""
    now = int(datetime.now(pytz.timezone('Asia/Shanghai')).timestamp())
    cursor.execute("UPDATE project_person_relation SET deleted_at = %s WHERE project_id = %s AND person_id = %s RETURNING id", (now, project_id, person_id))
    return cursor.fetchone()[0]
